package com.dao;

import com.entity.Td_tbreadlog;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.*;

import java.util.List;

public interface Td_tbreadlogDao extends JpaRepository<Td_tbreadlog,Integer> {

    /**
     * 查询本次学习记录
     * @param id
     * @return
     */
    @Query(value = "select * from td_tbreadlog where fd_id=?",nativeQuery = true)
    public Td_tbreadlog getByFd_id(int id);

    /**
     * 根据咨询id和用户id增加一次已读次数
     * @param teamId 课程id
     * @param loginId 用户id
     * @return
     */
    @Query(value = "update Td_tbreadlog set fd_readtotal=fd_readtotal+1,fd_ where Fd_teamid=? and Fd_loginid=?",nativeQuery = true)
    @Modifying
    public int addReadTotal(int teamId,int loginId);

    /**
     * 根据用户id和课程id查询阅读记录
     * @param teamId 课程id
     * @param loginId 用户id
     * @return
     */
    @Query(value = "select * from Td_tbreadlog where fd_teamid=? and fd_loginId=?",nativeQuery = true)
    public Td_tbreadlog getByTeamId8LoginId(int teamId,int loginId);

    /**
     * 获取阅读记录最多的三条
     * @return  0=id    1=标题    2=创建日期  3=标题图   4=阅读数
     */
    @Query(value = "SELECT\n" +
            "\tTOP 3 a.fd_id,a.FD_Abbrev,\n" +
            "\ta.FD_NewTime,\n" +
            "\ta.FD_NewsImg,\n" +
            "\tb.readTotal\n" +
            "FROM\n" +
            "\tTD_Team a,\n" +
            "\t(\n" +
            "\t\tSELECT\n" +
            "\t\t\tSUM (Fd_readtotal) AS 'readTotal',\n" +
            "\t\t\tb1.Fd_teamid\n" +
            "\t\tFROM\n" +
            "\t\t\ttd_tbreadlog b1\n" +
            "\t\tGROUP BY\n" +
            "\t\t\tb1.Fd_teamid\n" +
            "\t) b\n" +
            "WHERE\n" +
            "\ta.FD_ID = b.Fd_teamid\n" +
            "ORDER BY\n" +
            "\tb.readTotal DESC",nativeQuery = true)
    public List<Object[]> getThreeTeamByMaxTotal();

    /**
     * 根据阅读次数从高到低排序所有课程(区分出该用户是否已读)
     * @param loginId 用户id
     * @return 0=id 1=标题    2=标题图   3=总阅读次数 4=该用户阅读次数
     */
    @Query(value = "SELECT " +
            " a.fd_id, " +
            " a.FD_Abbrev, " +
            " a.FD_NewTime, " +
            " a.FD_NewsImg, " +
            " b.readTotal, " +
            " c.Fd_readtotal " +
            "FROM " +
            " TD_Team a " +
            "LEFT JOIN ( " +
            " SELECT " +
            "  SUM (Fd_readtotal) AS 'readTotal', " +
            "  b1.Fd_teamid " +
            " FROM " +
            "  td_tbreadlog b1 " +
            " GROUP BY " +
            "  b1.Fd_teamid " +
            ") b ON a.fd_id = b.Fd_teamid " +
            "LEFT JOIN td_tbreadlog c ON b.Fd_teamid = c.Fd_teamid and c.fd_loginId=? ," +
            " TD_NewsType newstype " +
            "WHERE " +
            " a.FD_TypeId = newstype.FD_TypeId " +
            "AND newstype.FD_FatherLevelID = 18 " +
            "ORDER BY " +
            " b.readTotal DESC",nativeQuery = true)
    public List<Object[]> getTeamByMaxTotal(int loginId,Pageable Pageable);

    /**
     * 获取该分类下的所有课程并按照创建时间从高到低排序
     * @param loginId 用户id
     * @param typeId 分类id
     * @param pageable
     * @return 0=id,1=标题,2=创建日期，3=标题图，4=阅读次数
     */
    @Query(value = "SELECT " +
            " team.fd_id, " +
            " team.FD_Abbrev, " +
            " team.FD_NewTime, " +
            " team.FD_NewsImg, " +
            " b.readTotal, " +
            " c.Fd_readtotal " +
            "FROM " +
            " TD_Team team " +
            "LEFT JOIN ( " +
            " SELECT " +
            "  SUM (Fd_readtotal) AS 'readTotal', " +
            "  b1.Fd_teamid " +
            " FROM " +
            "  td_tbreadlog b1 " +
            " GROUP BY " +
            "  b1.Fd_teamid " +
            ") b ON team.FD_ID = b.Fd_teamid " +
            "LEFT JOIN td_tbreadlog c ON b.Fd_teamid = c.Fd_teamid and c.fd_loginId=? " +
            "WHERE " +
            " team.FD_TypeId = ? " +
            "ORDER BY " +
            " team.FD_NewTime DESC",nativeQuery = true)
    public List<Object[]> getTeamByTypeDescCreateTime(int loginId,int typeId,Pageable pageable);

    /**
     * 获取最新三条记录
     * @return 0=id 1=标题    2=创建时间  3=标题图   4=阅读次数
     */
    @Query(value = "SELECT " +
            " top 3 a.fd_id, " +
            " a.FD_Abbrev, " +
            " a.FD_NewTime, " +
            " a.FD_NewsImg, " +
            " b.readTotal, " +
            " c.Fd_readtotal " +
            "FROM " +
            " TD_Team a " +
            "LEFT JOIN ( " +
            " SELECT " +
            "  SUM (Fd_readtotal) AS 'readTotal', " +
            "  b1.Fd_teamid " +
            " FROM " +
            "  td_tbreadlog b1 " +
            " GROUP BY " +
            "  b1.Fd_teamid " +
            ") b ON a.fd_id = b.Fd_teamid " +
            "LEFT JOIN td_tbreadlog c ON b.Fd_teamid = c.Fd_teamid, " +
            " TD_NewsType newstype " +
            "WHERE " +
            " a.FD_TypeId = newstype.FD_TypeId " +
            "AND newstype.FD_FatherLevelID = 18 " +
            "ORDER BY " +
            " a.FD_NewTime DESC",nativeQuery = true)
    public List<Object[]> getThreeNew();

}
